﻿//using BigMonitoringWarningApi.Model.ApiResult;
//using GYCTOAManagementSystem.Models.Enum;
//using GYCTOAManagementSystemNewApi.Model;
//using GYCTOAManagementSystemNewApi.Model.Enum;
//using GYCTOAManagementSystemNewApi.Model.Tool;
//using GYCTOAManagementSystemNewApi.Public;
//using Microsoft.AspNetCore.Authorization;
//using Microsoft.AspNetCore.Http;
//using Microsoft.AspNetCore.Mvc;
//using Microsoft.EntityFrameworkCore;
//using Microsoft.Extensions.Logging;
//using Newtonsoft.Json;
//using Newtonsoft.Json.Linq;
//using System;
//using System.Collections.Generic;
//using System.Linq;
//using System.Threading.Tasks;

//namespace GYCTOAManagementSystemNewApi.Controllers
//{
//   /// <summary>
//   /// 采购管理
//   /// </summary>
//    [Route("api/[controller]")]
//    [ApiController]
//    [Authorize]
//    public class ReportController : Controller
//    {
//        private readonly ILogger<ReportController> _logger;
//        DBContext _db;
//        PublicTool _publictool;
//        public ReportController(ILogger<ReportController> logger, DBContext db)
//        {
//            _logger = logger;
//            _db = db;
//            _publictool = new PublicTool(_db);
//        }
//        #region **********************入库单**********************
//        /// <summary>
//        /// 根据id，获取所有的入库物资详细记录，关联物资表和采购单
//        /// </summary>
//        /// <returns></returns>
//        [HttpPost("GetDetialInboundRecord")]
//        public JsonResult GetDetialInboundRecord([FromBody] int[] ids)
//        {
//            List<InboundRecord> records = _db.InboundRecord.Where(x => ids.Contains(x.Id)).ToList();
//            var devices = _db.Device.Where(x => !x.Delete).ToList();
//            var purchases = _db.Purchase.Where(x => !x.Delete).ToList();
//            var purchaseGoods = _db.PurchaseGoods.Where(x => !x.Delete).ToList();
//            var query = from i in records
//                        join d in devices on i.DeviceId equals d.Id into aa
//                        from aadata in aa.DefaultIfEmpty()
//                        join io in purchases on i.PurchaseNumber equals io.PurchaseNumber into bb
//                        from bbdata in bb.DefaultIfEmpty()
//                        join g in purchaseGoods on new { PurchaseNumber = i.PurchaseNumber, DeviceId = i.DeviceId } equals new { PurchaseNumber = g.PurchaseNumber, DeviceId = g.DeviceId } into gg
//                        from ggdata in gg.DefaultIfEmpty()
//                        select new { i.Id, i.Price, i.Remark, i.TotalPrice, i.Amount, i.StockAmount, i.DamageAmount, i.ConsumeAmount, i.Delete, i.DeviceId, i.InboundNumber, i.outboundAmount, aadata.DeviceName, aadata.DeviceNumber, aadata.DeviceCategoryIdOne, aadata.DeviceCategoryIdTwo, aadata.DeviceModel, aadata.Unit, DepartmentId = bbdata == null ? 0 : bbdata.DepartmentId, ProjectId = bbdata == null ? 0 : bbdata.ProjectId, VendorInf = bbdata == null ? 0 : bbdata.VendorInf, ReviewTotalPrice = ggdata == null ? 0 : ggdata.TotalPrice };
//            var list = query.ToList();
//            return Json(list);
//        }
//        /// <summary>
//        /// 入库单报表
//        /// </summary>
//        /// <param name="InboundNumber"></param>
//        /// <returns></returns>
//        [HttpGet("InboundOrder")]
//        public JsonResult InboundOrder(string InboundNumber)
//        {
//            var obj = _db.InboundOrder.Where(l => l.InboundNumber == InboundNumber).FirstOrDefault();
//            obj.inboundRecord = _db.InboundRecord.Where(x => x.InboundNumber == obj.InboundNumber && !x.Delete).ToList();
//            AuditRule auditRule = PublicTool.GetAuditRule(PublicTool.ModuleEnum_Inbound, obj.OperatId, _db);
//            return Json(new { obj, auditRule });
//        }
//        #endregion

//        #region  **********************出库单**********************
//        /// <summary>
//        /// 出库单报表
//        /// </summary>
//        /// <param name="OutboundNumber"></param>
//        /// <returns></returns>
//        [HttpGet("OutboundOrder")]
//        public JsonResult OutboundOrder(string OutboundNumber)
//        {
//            var obj = _db.OutboundOrder.Where(l => l.OutboundNumber == OutboundNumber).FirstOrDefault();
//            obj.outboundRecord = _db.OutboundRecord.Where(x => x.OutboundNumber == obj.OutboundNumber && !x.Delete).ToList();
//            AuditRule auditRule = PublicTool.GetAuditRule(PublicTool.ModuleEnum_Outbound, obj.EmployeeId, _db);
//            return Json(new { obj, auditRule });
//        }
//        /// <summary>
//        /// 根据id，获取所有的出库物资详细记录，关联物资表
//        /// </summary>
//        /// <returns></returns>
//        [HttpGet("GetDetialOutboundRecord")]
//        public JsonResult GetDetialOutboundRecord(string OutboundNumber)
//        {
//            List<OutboundRecord> records = _db.OutboundRecord.Where(x => x.OutboundNumber == OutboundNumber && !x.Delete).ToList();
//            foreach (var d in records)
//            {
//                d.VendorInfName = "";
//                var RelationInbound = d.RelationInbound;
//                if (!string.IsNullOrEmpty(RelationInbound))
//                {
//                    var InboundRecords = _db.InboundRecord.Where(x => RelationInbound.Contains(x.Id.ToString()) && !x.Delete).ToList();
//                    foreach (var r in InboundRecords)
//                    {
//                        if (!string.IsNullOrEmpty(r.PurchaseNumber))
//                        {
//                            var pu = _db.Purchase.Where(x => x.PurchaseNumber == r.PurchaseNumber).FirstOrDefault();
//                            var customer = _db.Customer.Where(y => y.Id == pu.VendorInf).FirstOrDefault();
//                            if (customer != null)
//                                d.VendorInfName += customer.CustomerCompany;
//                        }
//                    }
//                }
//            }//构造出库记录商品关联的供应商信息
//            var RelationInbounds = records.Select(x => x.RelationInbound).ToList();
//            var devices = _db.Device.Where(x => !x.Delete).ToList();
//            var query = from i in records
//                        join d in devices on i.DeviceId equals d.Id
//                        select new { i.Id, i.DeviceId, d.Price, i.Amount, i.IsReturn, i.RelationInbound, i.RelationInboundAmount, i.Remark, i.TotalPrice, d.DeviceName, d.DeviceNumber, d.DeviceModel, d.StockAmount, d.Unit, i.VendorInfName };
//            var list = query.ToList();

//            return Json(list);
//        }
//        #endregion

//        #region  **********************归还单**********************
//        /// <summary>
//        /// 出库单报表
//        /// </summary>
//        /// <param name="ReturnOrderNumber"></param>
//        /// <returns></returns>
//        [HttpGet("ReturnOrder")]
//        public JsonResult ReturnOrder(string ReturnOrderNumber)
//        {
//            var obj = _db.ReturnOrder.Where(l => l.ReturnOrderNumber == ReturnOrderNumber).FirstOrDefault();
//            obj.returnRecord = _db.ReturnRecord.Where(x => x.ReturnOrderNumber == obj.ReturnOrderNumber && !x.Delete).ToList();
//            AuditRule auditRule = PublicTool.GetAuditRule(PublicTool.ModuleEnum_Return, obj.OperatId, _db);
//            return Json(new { obj, auditRule });
//        }
//        /// <summary>
//        /// 根据id，获取所有的出物资详细记录，关联物资表
//        /// </summary>
//        /// <returns></returns>
//        [HttpGet("GetDetialReturnRecord")]
//        public JsonResult GetDetialReturnRecord(string ReturnOrderNumber)
//        {
//            var data = _db.ReturnOrder.Where(l => l.ReturnOrderNumber == ReturnOrderNumber).FirstOrDefault();
//            var returnRecord = _db.ReturnRecord.Where(x => x.ReturnOrderNumber == data.ReturnOrderNumber && !x.Delete).ToList();
//            var outboundRecordId = returnRecord.Select(x => x.OutboundRecordId).ToList();
//            var obj = _db.OutboundRecord.Where(x => outboundRecordId.Contains(x.Id) && !x.Delete).ToList();
//            var query = from re in returnRecord
//                        join o in obj on re.OutboundRecordId equals o.Id
//                        join d in _db.Device on o.DeviceId equals d.Id
//                        select new
//                        {
//                            re.Id,
//                            re.OutboundRecordId,
//                            o.OutboundNumber,
//                            o.DeviceId,
//                            o.Amount,
//                            o.TotalPrice,
//                            re.ConsumeAmount,
//                            re.DamageAmount,
//                            re.ReturnAmount,
//                            WaitReturnAmount = o.Amount - o.ReturnAmount - o.ConsumeAmount - o.DamageAmount,
//                            d.DeviceName,
//                            d.DeviceNumber,
//                            d.Unit,
//                            data.Remark
//                        };
//            var list = query.ToList();
//            return Json(list);
//        }
//        #endregion

//        #region **********************申购单**********************
//        /// <summary>
//        /// 申购单报表
//        /// </summary>
//        /// <param name="ApplyPurchaseNumber"></param>
//        /// <returns></returns>
//        [HttpGet("ApplyPurchaseOrder")]
//        public JsonResult ApplyPurchaseOrder(string ApplyPurchaseNumber)
//        {
//            var obj = _db.ApplyPurchase.Where(l => l.ApplyPurchaseNumber == ApplyPurchaseNumber).FirstOrDefault();
//            AuditRule auditRule = PublicTool.GetAuditRule(PublicTool.ModuleEnum_ApplyPurchase, obj.ApplyUserId, _db);
//            return Json(new { obj, auditRule });
//        }
//        /// <summary>
//        /// 申购单下详细物资
//        /// </summary>
//        /// <returns></returns>
//        [HttpGet("GetDetialApplyPurchaseGoods")]
//        public JsonResult GetDetialApplyPurchaseGoods(string ApplyPurchaseNumber)
//        {
//            List<ApplyPurchaseGoods> goods = _db.ApplyPurchaseGoods.Where(x => x.ApplyPurchaseNumber == ApplyPurchaseNumber && !x.Delete).ToList();
//            var devices = _db.Device.Where(x => !x.Delete).ToList();
//            var query = from g in goods
//                        join d in devices on g.DeviceId equals d.Id
//                        select new { g.Id, g.DeviceId, g.Amount, g.ReferenceWebsite, g.ReviewUnitPrice, g.TotalPrice, d.DeviceName, d.DeviceModel, d.Unit, g.ArrivalAddress };
//            var list = query.ToList();
//            return Json(list);
//        }
//        #endregion

//        #region **********************采购单**********************
//        /// <summary>
//        /// 采购单下详细物资
//        /// </summary>
//        /// <returns></returns>
//        [HttpGet("GetDetialPurchaseGoods")]
//        public JsonResult GetDetialPurchaseGoods(string PurchaseNumber)
//        {
//            List<PurchaseGoods> goods = _db.PurchaseGoods.Where(x => x.PurchaseNumber == PurchaseNumber && !x.Delete && !x.IsCancel).ToList();
//            var devices = _db.Device.Where(x => !x.Delete).ToList();
//            var query = from o in goods
//                        join d in devices on o.DeviceId equals d.Id
//                        select new
//                        {
//                            o.Id,
//                            o.DeviceId,
//                            o.PurchaseNumber,
//                            o.Amount,
//                            o.ReviewUnitPrice,
//                            o.TotalPrice,
//                            o.SurveyUnitPrice1,
//                            o.SurveyUnitPrice2,
//                            o.SurveyUnitPrice3,
//                            o.ActualPrice,
//                            o.IsInbound,
//                            o.IsCancel,
//                            o.InboundAmount,
//                            o.ArrivalAddress,
//                            d.DeviceNumber,
//                            d.DeviceCategoryIdOne,
//                            d.DeviceCategoryIdTwo,
//                            d.DeviceName,
//                            d.DeviceModel,
//                            d.Unit,
//                            d.StockAmount,
//                        };
//            var list = query.ToList();
//            return Json(list);
//        }
//        [HttpGet("PurchaseOrder")]
//        public JsonResult PurchaseOrder(string PurchaseNumber)
//        {
//            var obj = _db.Purchase.Where(l => l.PurchaseNumber == PurchaseNumber).FirstOrDefault();
//            obj.addPurchase = _db.PurchaseGoods.Where(x => x.PurchaseNumber == obj.PurchaseNumber && !x.Delete && !x.IsCancel).ToList();
//            //AuditRule auditRule = GetAuditRule(PublicTool.ModuleEnum_Purchase, obj.ApplyUserId);
//            //20220328根据新业务逻辑，取消申购后，通过采购人获取审批规则
//            AuditRule auditRule = PublicTool.GetAuditRule(PublicTool.ModuleEnum_Purchase, obj.PurchaseUserId, _db);
//            return Json(new { obj, auditRule });
//        }
//        #endregion


//        #region 报表管理财务报表模块下财务汇总明细表、财务成本表、工资表
//        #region 财务汇总明细报表
//        /// <summary>
//        /// 财务汇总明细报表数据
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="DepartmentId"></param>
//        /// <param name="ProjectId"></param>
//        /// <param name="ProjectType"></param>
//        /// <param name="FinanceType"></param>
//        /// <returns></returns>
//        [HttpPost("GetFinanceDetailReportData")]
//        public JsonResult GetFinanceDetailReportData([FromBody] dynamic formData)
//        {
//            //(string StartDate, string EndDate, int DepartmentId = 0, int ProjectId = 0, int ProjectType = 0, int FinanceType = 0)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = from["Start"].ToString();
//            string EndDate = from["End"].ToString();
//            int DepartmentId = Convert.ToInt32(from["Department"]);
//            int ProjectId = Convert.ToInt32(from["Project"]);
//            int ProjectType = Convert.ToInt32(from["ProjectType"]);
//            int FinanceType = Convert.ToInt32(from["FinanceType"]);

//            var data = new List<dynamic>();
//            var projectIds = _db.Project/*.Where(x => x.ProjectType == ProjectType)*/.Select(y => y.Id).ToList();
//            #region 构造借款数据
//            if (FinanceType == 0 || FinanceType == (int)FinanceLoanResEnum.IndividualLoan)
//            {
//                var sqlLoan = "select * from financeloan where 1=1 and IsRefuse=0  ";
//                if (DepartmentId > 0)
//                {
//                    sqlLoan += "and Department='" + DepartmentId + "' ";
//                    if (ProjectId == 0)
//                    {
//                        sqlLoan += "and Project=0 ";
//                    }
//                }
//                if (ProjectId > 0)
//                {
//                    sqlLoan += "and Project='" + ProjectId + "' ";
//                }
//                if (StartDate != "")
//                {
//                    sqlLoan += "and Date(BorrowTime) >='" + StartDate + "' ";
//                }
//                if (EndDate != "")
//                {
//                    sqlLoan += "and Date(BorrowTime) <='" + EndDate + "' ";
//                }
//                var queryLoan = _db.FinanceLoan.FromSqlRaw(sqlLoan).Where(x => !x.Delete && x.IsAuditEnd).ToList();
//                //if (ProjectType != 0)
//                //{
//                //    queryLoan = queryLoan.Where(x => projectIds.Contains(x.Project)).ToList();
//                //}
//                //List<FinanceLoan> datas = new List<FinanceLoan>();
//                //foreach (var d in queryLoan)
//                //{
//                //    AuditRule auditRule = Public.GetAuditRule(Public.ModuleEnum_Loan, d.Borrower);
//                //    if (d.State >= Math.Pow(2, auditRule.AuditPeopleNumber))
//                //    {
//                //        datas.Add(d);
//                //    }
//                //}
//                var LoanData = from a in queryLoan
//                               select new { Date = a.BorrowTime, Number = a.LoanNumber, /*a.Department, a.Project, a.Payee, a.PayeeType, a.FinanceType,*/ CollectMoney = 0, LoanMoney = a.Money, ResMoney = 0, DifferenceMoney = 0/*, a.ApprovalMoney */};
//                data.AddRange(LoanData);
//            }
//            #endregion

//            #region 构造报销数据
//            if (FinanceType == 0 || FinanceType == (int)FinanceLoanResEnum.Reimbursement)
//            {
//                var sqlRes = "select * from financereimbursement where 1=1 and IsRefuse=0 ";
//                if (DepartmentId > 0)
//                {
//                    sqlRes += "and Department='" + DepartmentId + "' ";
//                    if (ProjectId == 0)
//                    {
//                        sqlRes += "and Project=0 ";
//                    }
//                }
//                if (ProjectId > 0)
//                {
//                    sqlRes += "and Project='" + ProjectId + "' ";
//                }
//                if (StartDate != "")
//                {
//                    sqlRes += "and Date(BorrowTime) >='" + StartDate + "' ";
//                }
//                if (EndDate != "")
//                {
//                    sqlRes += "and Date(BorrowTime) <='" + EndDate + "' ";
//                }
//                var queryRes = _db.FinanceReimbursement.FromSqlRaw(sqlRes).Where(x => !x.Delete && x.IsAuditEnd).ToList();
//                if (ProjectType != 0)
//                {
//                    queryRes = queryRes.Where(x => projectIds.Contains(x.Project)).ToList();
//                }
//                //List<FinanceReimbursement> Resdatas = new List<FinanceReimbursement>();
//                //foreach (var d in queryRes)
//                //{
//                //    AuditRule auditRule = Public.GetAuditRule(Public.ModuleEnum_Reim, d.Borrower);
//                //    if (d.State >= Math.Pow(2, auditRule.AuditPeopleNumber))
//                //    {
//                //        Resdatas.Add(d);
//                //    }
//                //}
//                var ResData = from a in queryRes
//                              select new { Date = a.BorrowTime, Number = a.ReimNumber, /*a.Department,*/ a.Project, /*a.Payee, a.PayeeType, a.FinanceType,*/ CollectMoney = 0, LoanMoney = 0, ResMoney = a.Money, /*DifferenceMoney = a.Difference,*/ /*ApprovalMoney = a.Difference*/ };//20210413将财务核准金额改为成本合计，防止最后一列重复计算
//                data.AddRange(ResData);
//            }
//            #endregion

//            #region 构造收款数据
//            if (FinanceType == 0 || FinanceType == (int)FinanceLoanResEnum.Collection)
//            {
//                var sqlColl = "select * from financecollection where 1=1 ";
//                if (ProjectId > 0)
//                {
//                    sqlColl += "and ProjectId='" + ProjectId + "' ";
//                }
//                if (StartDate != "")
//                {
//                    sqlColl += "and Date(CollectionDate) >='" + StartDate + "' ";
//                }
//                if (EndDate != "")
//                {
//                    sqlColl += "and Date(CollectionDate) <='" + EndDate + "' ";
//                }
//                var queryColl = _db.FinanceCollection.FromSqlRaw(sqlColl).Where(x => !x.Delete).ToList();
//                if (ProjectType != 0)
//                {
//                    queryColl = queryColl.Where(x => projectIds.Contains(x.ProjectId)).ToList();
//                }
//                var CollData = from a in queryColl
//                               select new { Date = a.CollectionDate, Number = a.CollectionNumber, Department = 0, Project = a.ProjectId, Payee = a.CustomerId, PayeeType = 1, FinanceType = 16, CollectMoney = a.Money, LoanMoney = 0, ResMoney = 0, DifferenceMoney = 0, ApprovalMoney = 0 };
//                data.AddRange(CollData);
//            }
//            #endregion

//            return Json(data.OrderByDescending(x => x.Date));
//        }
//        #endregion

//        #region 财务成本报表
//        /// <summary>
//        /// 财务成本报表数据
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="apply"></param>
//        /// <returns></returns>
//        [HttpPost("GetFinanceCostReportData")]
//        public JsonResult GetFinanceCostReportData([FromBody] dynamic formData)     
//        {
//            //(string StartDate, string EndDate, int DepartmentId = 0, int ProjectId = 0, int ProjectType = 0)

//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = from["Start"].ToString();
//            string EndDate = from["End"].ToString();
//            int DepartmentId = Convert.ToInt32(from["Department"]);
//            int ProjectId = Convert.ToInt32(from["Project"]);
//            int ProjectType = Convert.ToInt32(from["ProjectType"]);

//            var data = new List<dynamic>();
//            var projectIds = _db.Project/*.Where(x => x.ProjectType == ProjectType)*/.Select(y => y.Id).ToList();
//            #region 构造借款数据
//            var sqlLoan = "select * from financeloan where 1=1 and IsRefuse=0  ";
//            //var sqlLoan = "select * from financeloan where 1=1 ";
//            if (DepartmentId > 0)
//            {
//                sqlLoan += "and Department='" + DepartmentId + "' ";
//                if (ProjectId == 0)
//                {
//                    sqlLoan += "and Project=0 ";
//                }
//            }
//            if (ProjectId > 0)
//            {
//                sqlLoan += "and Project='" + ProjectId + "' ";
//            }
//            if (StartDate != "")
//            {
//                sqlLoan += "and Date(BorrowTime) >='" + StartDate + "' ";
//            }
//            if (EndDate != "")
//            {
//                sqlLoan += "and Date(BorrowTime) <='" + EndDate + "' ";
//            }
//            var queryLoan = _db.FinanceLoan.FromSqlRaw(sqlLoan).Where(x => !x.Delete && x.IsAuditEnd).ToList();
//            if (ProjectType != 0)
//            {
//                queryLoan = queryLoan/*.Where(x => projectIds.Contains(x.Project))*/.ToList();
//            }
//            //List<FinanceLoan> datas = new List<FinanceLoan>();
//            //foreach (var d in queryLoan)
//            //{
//            //    AuditRule auditRule = Public.GetAuditRule(Public.ModuleEnum_Loan, d.Borrower);
//            //    if (d.State >= Math.Pow(2, auditRule.AuditPeopleNumber))
//            //    {
//            //        datas.Add(d);
//            //    }
//            //}
//            var LoanData = from a in queryLoan
//                           select new { a.BorrowTime, /*a.Department, a.Project, CostOne = a.ReversalMoney, CostTwo = a.IsFinishReversalReal ? 0 : a.ApprovalMoney - a.ReversalMoney, AllCost = a.IsFinishReversalReal ? a.ReversalMoney : a.ApprovalMoney, Number = "借款:" + a.LoanNumber*/ };
//            data.AddRange(LoanData);
//            #endregion

//            #region 构造报销数据
//            var sqlRes = "select * from financereimbursement where 1=1 and IsRefuse=0 and ReimMethod=" + (int)FinanceReimType.Advanced + " ";
//            //var sqlRes = "select * from financereimbursement where 1=1 and ReimMethod=" + (int)FinanceReimType.Advanced + " ";
//            if (DepartmentId > 0)
//            {
//                sqlRes += "and Department='" + DepartmentId + "' ";
//                if (ProjectId == 0)
//                {
//                    sqlRes += "and Project=0 ";
//                }
//            }
//            if (ProjectId > 0)
//            {
//                sqlRes += "and Project='" + ProjectId + "' ";
//            }
//            if (StartDate != "")
//            {
//                sqlRes += "and Date(BorrowTime) >='" + StartDate + "' ";
//            }
//            if (EndDate != "")
//            {
//                sqlRes += "and Date(BorrowTime) <='" + EndDate + "' ";
//            }
//            var queryRes = _db.FinanceReimbursement.FromSqlRaw(sqlRes).Where(x => !x.Delete && x.IsAuditEnd).ToList();
//            if (ProjectType != 0)
//            {
//                queryRes = queryRes.Where(x => projectIds.Contains(x.Project)).ToList();
//            }
//            //List<FinanceReimbursement> Resdatas = new List<FinanceReimbursement>();
//            //foreach (var d in queryRes)
//            //{
//            //    AuditRule auditRule = Public.GetAuditRule(Public.ModuleEnum_Reim, d.Borrower);
//            //    if (d.State >= Math.Pow(2, auditRule.AuditPeopleNumber))
//            //    {
//            //        Resdatas.Add(d);
//            //    }
//            //}
//            var ResData = from a in queryRes
//                          select new { a.BorrowTime, a.Department, a.Project, CostOne = a.ApprovalMoney, CostTwo = 0, AllCost = a.ApprovalMoney, Number = "报销:" + a.ReimNumber };
//            data.AddRange(ResData);
//            #endregion
//            return Json(data.OrderBy(x => x.BorrowTime));
//        }
//        #endregion

//        #region  财务银行流水报表
//        /// <summary>
//        /// 财务银行流水报表数据
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="FinanceBankCardId"></param>
//        /// <param name="DepartmentId"></param>
//        /// <param name="ProjectId"></param>
//        /// <param name="FinanceType"></param>
//        /// <param name="UserId"></param>
//        /// <param name="CustomerId"></param>
//        /// <returns></returns>
//        [HttpPost("GetFinanceFlowWaterReportData")]
//        public JsonResult GetFinanceFlowWaterReportData([FromBody] dynamic formData)     
//        {
//             // (string StartDate, string EndDate, int FinanceBankCardId, int DepartmentId = 0,
//             //   int ProjectId = 0, int FinanceType = 0, int UserId = 0, int CustomerId = 0)
//            //(string StartDate, string EndDate, int FinanceBankCardId, int DepartmentId = 0, int ProjectId = 0, int FinanceType = 0, int UserId = 0, int CustomerId = 0)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = from["Start"].ToString();
//            string EndDate = from["End"].ToString();

//            int DepartmentId = Convert.ToInt32(from["Department"]);
//            int ProjectId = Convert.ToInt32(from["Project"]);
//            int FinanceType = Convert.ToInt32(from["FinanceType"]);
//            int FinanceBankCardId = Convert.ToInt32(from["FinanceBankCardId"]);
//            int UserId = Convert.ToInt32(from["UserId"]);
//            int CustomerId = Convert.ToInt32(from["CustomerId"]);

//            var data = new List<dynamic>();

//            #region 构造借款数据
//            if (FinanceType == 0 || FinanceType == (int)FinanceLoanResEnum.IndividualLoan)
//            {
//                var sqlLoan = "select * from financeloan where 1=1 and IsRefuse=0  ";
//                if (DepartmentId > 0)
//                {
//                    sqlLoan += "and Department='" + DepartmentId + "' ";
//                    if (ProjectId == 0)
//                    {
//                        sqlLoan += "and Project=0 ";
//                    }
//                }
//                if (ProjectId > 0)
//                {
//                    sqlLoan += "and Project='" + ProjectId + "' ";
//                }
//                if (StartDate != "")
//                {
//                    sqlLoan += "and Date(BorrowTime) >='" + StartDate + "' ";
//                }
//                if (EndDate != "")
//                {
//                    sqlLoan += "and Date(BorrowTime) <='" + EndDate + "' ";
//                }
//                if (FinanceBankCardId != -1)
//                {
//                    if (FinanceBankCardId == 0)//传入后台的数据是选中了现金
//                    {
//                        sqlLoan += "and TransferMethod='" + (int)FinanceTransferTypeEnum.Cash + "' ";
//                    }
//                    else
//                    {
//                        sqlLoan += "and FinanceBankId='" + FinanceBankCardId + "' ";
//                    }
//                }
//                if (CustomerId != 0)
//                {
//                    sqlLoan += "and PayeeType='" + (int)FinancePayeeTypeEnum.Customer + "' and Payee='" + CustomerId + "' ";
//                }
//                if (UserId != 0)
//                {
//                    sqlLoan += $"and ((PayeeType='" + (int)FinancePayeeTypeEnum.User + "' and Payee='" + UserId + "') or (PayeeType= '" + (int)FinancePayeeTypeEnum.Customer + "' and Borrower='" + UserId + "')) ";
//                }
//                var queryLoan = _db.FinanceLoan.FromSqlRaw(sqlLoan).Where(x => !x.Delete && x.IsAuditEnd).ToList();
//                //List<FinanceLoan> datas = new List<FinanceLoan>();
//                //foreach (var d in queryLoan)
//                //{
//                //    AuditRule auditRule = Public.GetAuditRule(Public.ModuleEnum_Loan, d.Borrower);
//                //    if (d.State >= Math.Pow(2, auditRule.AuditPeopleNumber))
//                //    {
//                //        datas.Add(d);
//                //    }
//                //}
//                var LoanData = from a in queryLoan
//                               select new { Date = a.BorrowTime, Number = "借款:" + a.LoanNumber, Borrower = a.Borrower, FinanceBankId = a.FinanceBankId, a.Department, a.Project, a.Payee, a.PayeeType, a.FinanceType, CollectMoney = 0, LoanMoney = a.Money, ResMoney = 0, DifferenceMoney = 0, a.ApprovalMoney, FlowWater = -(a.ApprovalMoney + a.ServiceCharge), AuditTime = PublicTool.GetAuditTime(a), ActualPaymentTime = a.ActualPaymentTime };
//                data.AddRange(LoanData);
//            }
//            #endregion

//            #region 构造报销数据
//            if (FinanceType == 0 || FinanceType == (int)FinanceLoanResEnum.Reimbursement)
//            {
//                var sqlRes = "select * from financereimbursement where 1=1 and IsRefuse=0 and Difference!=0 ";
//                if (DepartmentId > 0)
//                {
//                    sqlRes += "and Department='" + DepartmentId + "' ";
//                    if (ProjectId == 0)
//                    {
//                        sqlRes += "and Project=0 ";
//                    }
//                }
//                if (ProjectId > 0)
//                {
//                    sqlRes += "and Project='" + ProjectId + "' ";
//                }
//                if (StartDate != "")
//                {
//                    sqlRes += "and Date(BorrowTime) >='" + StartDate + "' ";
//                }
//                if (EndDate != "")
//                {
//                    sqlRes += "and Date(BorrowTime) <='" + EndDate + "' ";
//                }
//                if (FinanceBankCardId != -1)
//                {
//                    if (FinanceBankCardId == 0)//传入后台的数据是选中了现金
//                    {
//                        sqlRes += "and TransferMethod='" + (int)FinanceTransferTypeEnum.Cash + "' ";
//                    }
//                    else
//                    {
//                        sqlRes += "and FinanceBankId='" + FinanceBankCardId + "' ";
//                    }
//                }
//                if (CustomerId != 0)
//                {
//                    sqlRes += "and PayeeType='" + (int)FinancePayeeTypeEnum.Customer + "' and Payee='" + CustomerId + "' ";
//                }
//                if (UserId != 0)
//                {
//                    sqlRes += "and ((PayeeType='" + (int)FinancePayeeTypeEnum.User + "' and Payee='" + UserId + "') or (PayeeType='" + (int)FinancePayeeTypeEnum.Customer + "' and Borrower='" + UserId + "')) ";
//                }
//                var queryRes = _db.FinanceReimbursement.FromSqlRaw(sqlRes).Where(x => !x.Delete && x.IsAuditEnd).ToList();
//                //List<FinanceReimbursement> Resdatas = new List<FinanceReimbursement>();
//                //foreach (var d in queryRes)
//                //{
//                //    AuditRule auditRule = Public.GetAuditRule(Public.ModuleEnum_Reim, d.Borrower);
//                //    if (d.State >= Math.Pow(2, auditRule.AuditPeopleNumber))
//                //    {
//                //        Resdatas.Add(d);
//                //    }
//                //}
//                var ResData = from a in queryRes
//                              select new { Date = a.BorrowTime, Number = "报销:" + a.ReimNumber, Borrower = a.Borrower, FinanceBankId = a.FinanceBankId, a.Department, a.Project, a.Payee, a.PayeeType, a.FinanceType, CollectMoney = 0, LoanMoney = 0, ResMoney = a.Money, DifferenceMoney = a.Difference, a.ApprovalMoney, FlowWater = -(a.Difference + a.ServiceCharge), AuditTime = PublicTool.GetAuditTime(a), ActualPaymentTime = a.ActualPaymentTime };
//                data.AddRange(ResData);
//            }
//            #endregion

//            #region 构造收款数据
//            if (FinanceType == 0 || FinanceType == (int)FinanceLoanResEnum.Collection)
//            {
//                var sqlColl = "select * from financecollection where 1=1 ";
//                if (ProjectId > 0)
//                {
//                    sqlColl += "and ProjectId='" + ProjectId + "' ";
//                }
//                if (StartDate != "")
//                {
//                    sqlColl += "and Date(CollectionDate) >='" + StartDate + "' ";
//                }
//                if (EndDate != "")
//                {
//                    sqlColl += "and Date(CollectionDate) <='" + EndDate + "' ";
//                }
//                if (FinanceBankCardId != -1)
//                {
//                    sqlColl += "and FinanceBankId='" + FinanceBankCardId + "' ";
//                }
//                if (CustomerId != 0)
//                {
//                    sqlColl += "and CustomerId='" + CustomerId + "' ";
//                }
//                if (DepartmentId != 0 || UserId != 0)
//                {
//                    sqlColl += "and 1=2 ";
//                }
//                var queryColl = _db.FinanceCollection.FromSqlRaw(sqlColl).Where(x => !x.Delete).ToList();
//                var CollData = from a in queryColl
//                               select new { Date = a.CollectionDate, Number = "收款:" + a.CollectionNumber, Borrower = 0, FinanceBankId = a.FinanceBankId, Department = 0, Project = a.ProjectId, Payee = a.CustomerId, PayeeType = 1, FinanceType = 16, CollectMoney = a.Money, LoanMoney = 0, ResMoney = 0, DifferenceMoney = 0, ApprovalMoney = 0, FlowWater = a.Money, AuditTime = a.CollectionDate, ActualPaymentTime = a.CollectionDate };
//                data.AddRange(CollData);
//            }
//            #endregion

//            //var newdata = data.OrderByDescending(x=>x.AuditTime).ToList();

//            //return Json(data.OrderByDescending(x => x.AuditTime));
//            return Json(data.OrderByDescending(x => x.ActualPaymentTime).ThenByDescending(x=>x.AuditTime).ToList());
//        }
//        #endregion

//        #region 个人借款汇总表
//        /// <summary>
//        /// 个人借款汇总报表
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="Quit"></param>
//        /// <returns></returns>
//        [HttpPost("GetPersonLoanSumReportData")]
//        public JsonResult GetPersonLoanSumReportData([FromBody] dynamic formData)       
//        {
//            //(int LoanType, DateTime? StartDate, DateTime? EndDate, bool Quit)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            int LoanType = Convert.ToInt32(from["LoanType"]);
//            bool Quit = Convert.ToBoolean(from["Quit"].ToString());
//            DateTime StartDate = DateTime.Now;
//            DateTime EndDate = DateTime.Now;
//            if (from["Start"].ToString()!="") {
//                StartDate = Convert.ToDateTime(from["Start"].ToString());
//            }
//            if (from["End"].ToString() != "")
//            {
//                EndDate = Convert.ToDateTime(from["End"].ToString());
//            }

//            var data = new List<dynamic>();
//            var financeData = new List<dynamic>();
//            var LoanAll = _db.FinanceLoan.Where(x => x.IsAuditEnd && x.BorrowTime <= EndDate && !x.HasInvoice).ToList();
//            if (LoanType != 0)
//            {
//                LoanAll = LoanAll.Where(x => x.FinanceType == LoanType).ToList();
//            }
//            var loanNumbers = LoanAll.Select(x => x.LoanNumber).ToList();
//            var ReimAll = _db.FinanceReimbursement.Where(x => loanNumbers.Contains(x.LoanNumber) && x.IsAuditEnd && x.BorrowTime <= EndDate).ToList();
//            foreach (var l in LoanAll)
//            {
//                data.Add(new { l.Borrower, l.Department, l.Project, Type = 1, Number = l.LoanNumber, l.BorrowTime, loan = l.ApprovalMoney, reversal = 0 });
//            }
//            foreach (var r in ReimAll)
//            {
//                data.Add(new { r.Borrower, r.Department, r.Project, Type = 2, Number = r.ReimNumber, r.BorrowTime, loan = 0, reversal = r.ApprovalMoney - r.Difference });
//            }
//            data = data.OrderBy(x => x.BorrowTime).ToList();
//            //根据所需时间段内的借款人进行分组
//            IEnumerable<IGrouping<int, FinanceLoan>> group;
//            if (from["Start"].ToString() != "")
//            {
//                group = LoanAll.Where(x => x.BorrowTime >= StartDate).GroupBy(x => x.Borrower).ToList();
//            }
//            else
//            {
//                group = LoanAll.GroupBy(x => x.Borrower).ToList();
//            }
//            foreach (var g in group)
//            {
//                decimal loan = 0, reversal = 0, openBalance = 0, closBalance = 0;//冲账金额
//                var d = g.FirstOrDefault();
//                var loanData = data.Where(x => x.Borrower == d.Borrower).ToList();
//                if (from["Start"].ToString() != "")
//                {
//                    var openData = loanData.Where(x => x.BorrowTime < StartDate).ToList();
//                }
//                foreach (var l in loanData)
//                {
//                    if (from["Start"].ToString() == "")
//                    {
//                        loan += l.loan;
//                        reversal += l.reversal;
//                    }
//                    else
//                    {
//                        if (l.BorrowTime < StartDate)
//                        {
//                            openBalance += (l.loan - l.reversal);
//                        }
//                        else
//                        {
//                            loan += l.loan;
//                            reversal += l.reversal;
//                        }
//                    }
//                }
//                openBalance += _db.Users.Where(x => x.Id == d.Borrower).FirstOrDefault().OpenBalance;//加上OA上线使用之前的借款金额
//                closBalance = loan - reversal + openBalance;
//                financeData.Add(new { d.Borrower, openBalance, loan, reversal, closBalance });
//            }
//            var newData = from d in financeData
//                          join us in _db.Users on d.Borrower equals us.Id
//                          join de in _db.Department on us.DepartmentId equals de.Id
//                          select new
//                          {
//                              d.Borrower,
//                              d.openBalance,
//                              d.loan,
//                              d.reversal,
//                              d.closBalance,
//                              us.UserName,
//                              us.InternshipState,
//                              us.DepartmentId,
//                              de.DepartmentName,
//                          };
//            return Json(newData.OrderBy(x => x.DepartmentId).ThenBy(x => x.InternshipState));
//        }
//        #endregion

//        #region 项目回款台账报表
//        /// <summary>
//        /// 项目应收账款明细表老接口
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="apply"></param>
//        /// <returns></returns>
//        [HttpPost("GetProjectCollectionReportData")]
//        public JsonResult GetProjectCollectionReportData([FromBody] dynamic formData)        
//        {
//            // (string StartDate, string EndDate, int ProjectId, int ProjectType)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = from["Start"].ToString();
//            string EndDate = from["End"].ToString();
//            int ProjectId = Convert.ToInt32(from["Project"]);
//            int ProjectType = Convert.ToInt32(from["ProjectType"]);
//            int offset = Convert.ToInt32(j_token["currentPage"]);
//            int limit = Convert.ToInt32(j_token["pagesize"]);

//            var sql = "select * from Project where 1=1 ";
//            if (ProjectId > 0)
//            {
//                sql += "and Id='" + ProjectId + "' ";
//            }
//            if (StartDate != "")
//            {
//                sql += "and Date(CreateDate) >='" + StartDate + "' ";
//            }
//            if (EndDate != "")
//            {
//                sql += "and Date(CreateDate) <='" + EndDate + "' ";
//            }
//            if (ProjectType != 0)
//            {
//                sql += "and ProjectType='" + ProjectType + "' ";
//            }
//            var queryProject = _db.Project.FromSqlRaw(sql).Where(x => !x.Delete).ToList();
//            var queryInvoices = _db.FinanceInvoice.Where(x => !x.Delete && x.State == (int)FinanceInvoiceState.Billing).GroupBy(x => new { x.ProjectId }).Select(y => new
//            {
//                y.Key.ProjectId,
//                InvoiceMoney = y.Sum(x => x.TotalPrice),
//                RedMarkMoney = y.Sum(x => x.RedMarkMoney),
//                CollectionAmount = y.Sum(x => x.CollectionAmount),
//                RateMoney = y.Sum(x => (x.TotalPrice / (1 + (x.TaxRate / 100))) * (x.TaxRate / 100))
//            }).ToList();
//            List<ProjectFinanceReport> projectFinanceReports = new List<ProjectFinanceReport>();
//            foreach (var p in queryProject)
//            {
//                ProjectFinanceReport pfr = new ProjectFinanceReport();
//                pfr.Id = p.Id;
//                pfr.ProjectNumber = p.ProjectNumber;
//                pfr.ProjectName = p.ProjectName;
//                pfr.ContractId = p.ContractId;
//                pfr.OperatDate = p.OperatDate;
//                pfr.SettleMoney = p.SettleMoney;
//                pfr.Bond = p.Bond;
//                pfr.BadMoney = p.BadMoney;
//                pfr.WriteOffTax = p.WriteOffTax;
//                pfr.Remark = p.Remark;
//                var c = _db.Contract.Where(x => x.Id == p.ContractId).FirstOrDefault();
//                if (c != null)
//                {
//                    pfr.Title = c.Title;
//                    pfr.SignDate = c.SignDate;
//                    pfr.CustomerId = c.CustomerId;
//                    pfr.ContractPrice = c.ContractPrice;
//                }
//                //冲销税额与开票已收款金额
//                var queryInvoice = queryInvoices.Where(x => x.ProjectId == p.Id).FirstOrDefault();
//                if (queryInvoice != null)
//                {
//                    pfr.InvoiceMoney = queryInvoice.InvoiceMoney + p.CollectionAmount;
//                    pfr.CollectionAmount = queryInvoice.CollectionAmount + p.CollectionAmount;
//                    pfr.NoCollectionAmount = pfr.InvoiceMoney - pfr.CollectionAmount;
//                    pfr.AccountReceiv = pfr.ContractPrice - pfr.CollectionAmount;
//                    pfr.NoInvoiceMoney = pfr.ContractPrice - pfr.InvoiceMoney;
//                    pfr.SettleNoCollectionMoney = pfr.SettleMoney - pfr.CollectionAmount;
//                    pfr.RateMoney = queryInvoice.RateMoney;
//                    pfr.RedMarkMoney = queryInvoice.RedMarkMoney;
//                    pfr.TaxPay = pfr.RateMoney - p.WriteOffTax + p.WriteOffRate;
//                }
//                else
//                {
//                    pfr.CollectionAmount = p.CollectionAmount;
//                }
//                projectFinanceReports.Add(pfr);
//            }
//            var datas = projectFinanceReports.OrderByDescending(x => x.OperatDate).ToList();
//            var total = datas.Count;
//            var rows = datas.Skip(limit * (offset - 1)).Take(limit).ToList();
//            return Json(new { code = 0, msg = "", count = total, data = rows });
//        }
//        #endregion

//        #region 项目费用支出台账报表
//        /// <summary>
//        /// 项目费用支出台账报表
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="apply"></param>
//        /// <returns></returns>
//        [HttpPost("GetProjectExpensesReportData")]
//        public JsonResult GetProjectExpensesReportData([FromBody] dynamic formData)
//        {
//            //(int limit, int offset, string StartDate, string EndDate, int ProjectId, int ProjectType)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = from["Start"].ToString();
//            string EndDate = from["End"].ToString();
//            int ProjectId = Convert.ToInt32(from["Project"]);
//            int ProjectType = Convert.ToInt32(from["ProjectType"]);
//            int offset = Convert.ToInt32(j_token["currentPage"]);
//            int limit = Convert.ToInt32(j_token["pagesize"]);

//            var sql = "select * from Project where 1=1 ";
//            if (ProjectId > 0)
//            {
//                sql += "and Id='" + ProjectId + "' ";
//            }
//            if (StartDate != "")
//            {
//                sql += "and Date(CreateDate) >='" + StartDate + "' ";
//            }
//            if (EndDate != "")
//            {
//                sql += "and Date(CreateDate) <='" + EndDate + "' ";
//            }
//            if (ProjectType != 0)
//            {
//                sql += "and ProjectType='" + ProjectType + "' ";
//            }
//            var queryProject = _db.Project.FromSqlRaw(sql).Where(x => !x.Delete).ToList();
//            List<ProjectFinanceReport> projectFinanceReports = GetTotalCost(queryProject);
//            var datas = projectFinanceReports.OrderByDescending(x => x.OperatDate).ToList();
//            var total = datas.Count;
//            var rows = datas.Skip(limit * (offset - 1)).Take(limit).ToList();
//            return Json(new { code = 0, msg = "", count = total, data = rows });
//        }
//        #endregion

//        #region 项目人工费用支出台账报表
//        /// <summary>
//        /// 项目人工费用支出台账报表
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="apply"></param>
//        /// <returns></returns>
//        [HttpPost("GetProjectLaborExpensesReportData")]
//        public JsonResult GetProjectLaborExpensesReportData([FromBody] dynamic formData)
//        {
//            //(int limit, int offset, string StartDate, string EndDate, int ProjectId, int ProjectType)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = from["Start"].ToString();
//            string EndDate = from["End"].ToString();
//            int ProjectId = Convert.ToInt32(from["Project"]);
//            int ProjectType = Convert.ToInt32(from["ProjectType"]);
//            int offset = Convert.ToInt32(j_token["currentPage"]);
//            int limit = Convert.ToInt32(j_token["pagesize"]);

//            var sql = "select * from Project where 1=1 ";
//            if (ProjectId > 0)
//            {
//                sql += "and Id='" + ProjectId + "' ";
//            }
//            if (StartDate != "")
//            {
//                sql += "and Date(CreateDate) >='" + StartDate + "' ";
//            }
//            if (EndDate != "")
//            {
//                sql += "and Date(CreateDate) <='" + EndDate + "' ";
//            }
//            if (ProjectType != 0)
//            {
//                sql += "and ProjectType='" + ProjectType + "' ";
//            }
//            var queryProject = _db.Project.FromSqlRaw(sql).Where(x => !x.Delete).ToList();
//            List<ProjectFinanceReport> projectFinanceReports = new List<ProjectFinanceReport>();
//            foreach (var p in queryProject)
//            {
//                ProjectFinanceReport pfr = new ProjectFinanceReport();
//                pfr.Id = p.Id;
//                pfr.ProjectNumber = p.ProjectNumber;
//                pfr.ProjectName = p.ProjectName;
//                pfr.ContractId = p.ContractId;
//                pfr.OperatDate = p.OperatDate;
//                pfr.SettleMoney = p.SettleMoney;
//                var c = _db.Contract.Where(x => x.Id == p.ContractId).FirstOrDefault();
//                if (c != null)
//                {
//                    pfr.Title = c.Title;
//                    pfr.SignDate = c.SignDate;
//                    pfr.CustomerId = c.CustomerId;
//                    pfr.ContractPrice = c.ContractPrice;
//                }
//                var financeLoans = _db.FinanceLoan.Where(x => p.Id == x.Project && x.FinanceType == (int)FinanceLoanResEnum.PayApplication && !x.IsRefuse && !x.Delete).ToList();
//                var CopeWithLoanNumbers = financeLoans.Select(x => x.LoanNumber).ToList();
//                List<string> PaidLoanNumbers = new List<string>();
//                foreach (var f in financeLoans)
//                {
//                    if (f.IsAuditEnd)
//                    {
//                        PaidLoanNumbers.Add(f.LoanNumber);
//                    }
//                }
//                decimal CopeWithLabour = 0, PaidLabour = 0, LaborExpenses = 0;
//                if (CopeWithLoanNumbers.Count > 0)
//                {
//                    var CopeWithLabours = _db.FinanceDocDetail.Where(x => CopeWithLoanNumbers.Contains(x.SourceNumber) && x.SourceType == (int)FinanceDocDetailEnum.Loan && x.CategoryOne == (int)FinanceCategoryEnum.LaborCost).ToList();
//                    CopeWithLabour = CopeWithLabours.Sum(x => x.Money);
//                }
//                if (PaidLoanNumbers.Count > 0)
//                {
//                    var PaidLabours = _db.FinanceDocDetail.Where(x => PaidLoanNumbers.Contains(x.SourceNumber) && x.SourceType == (int)FinanceDocDetailEnum.Loan && x.CategoryOne == (int)FinanceCategoryEnum.LaborCost).ToList();
//                    PaidLabour = PaidLabours.Sum(x => x.Money);
//                    var LaborExpensess = _db.FinanceDocDetail.Where(x => PaidLoanNumbers.Contains(x.SourceNumber) && x.SourceType == (int)FinanceDocDetailEnum.Loan && x.CategoryOne == (int)FinanceCategoryEnum.Test).ToList();
//                    LaborExpenses = LaborExpensess.Sum(x => x.Money);
//                }
//                pfr.CopeWithLabour = CopeWithLabour + p.LaborCost;
//                pfr.PaidLabour = PaidLabour;
//                pfr.UnpaidLabour = CopeWithLabour - PaidLabour;
//                pfr.LaborExpenses = LaborExpenses;
//                projectFinanceReports.Add(pfr);
//            }
//            var datas = projectFinanceReports.OrderByDescending(x => x.OperatDate).ToList();
//            var total = datas.Count;
//            var rows = datas.Skip(limit * (offset - 1)).Take(limit).ToList();
//            return Json(new { code = 0, msg = "", count = total, data = rows });
//        }
//        #endregion

//        #region 项目毛利报表
//        /// <summary>
//        /// 项目毛利报表
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="apply"></param>
//        /// <returns></returns>
//        [HttpPost("GetProjectGrossProfitReportData")]
//        public JsonResult GetProjectGrossProfitReportData([FromBody] dynamic formData)   
//        {
//            //(int limit, int offset, string StartDate, string EndDate, int ProjectId, int ProjectType)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = from["Start"].ToString();
//            string EndDate = from["End"].ToString();
//            int ProjectId = Convert.ToInt32(from["Project"]);
//            int ProjectType = Convert.ToInt32(from["ProjectType"]);
//            int offset = Convert.ToInt32(j_token["currentPage"]);
//            int limit = Convert.ToInt32(j_token["pagesize"]);

//            var sql = "select * from Project where 1=1 ";
//            if (ProjectId > 0)
//            {
//                sql += "and Id='" + ProjectId + "' ";
//            }
//            if (StartDate != "")
//            {
//                sql += "and Date(CreateDate) >='" + StartDate + "' ";
//            }
//            if (EndDate != "")
//            {
//                sql += "and Date(CreateDate) <='" + EndDate + "' ";
//            }
//            if (ProjectType != 0)
//            {
//                sql += "and ProjectType='" + ProjectType + "' ";
//            }
//            var queryProject = _db.Project.FromSqlRaw(sql).Where(x => !x.Delete).ToList();
//            List<ProjectFinanceReport> projectFinanceReports = GetTotalCost(queryProject);
//            var datas = projectFinanceReports.OrderByDescending(x => x.OperatDate).ToList();
//            var total = datas.Count;
//            var rows = datas.Skip(limit * (offset - 1)).Take(limit).ToList();
//            return Json(new { code = 0, msg = "", count = total, data = rows });
//        }
//        #endregion

//        #region 申购报表
//        /// <summary>
//        /// 申购报表数据
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="apply"></param>
//        /// <returns></returns>
//        [HttpPost("GetDetialApplyPurchaseReportData")]
//        public JsonResult GetDetialApplyPurchaseReportData([FromBody] dynamic formData)      
//        {
//            //(string StartDate, string EndDate, ApplyPurchase applyPurchase)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            var from = j_token["from"];
//            string StartDate = j_token["Start"].ToString();
//            string EndDate = j_token["End"].ToString();
//            ApplyPurchase applyPurchase = JsonConvert.DeserializeObject<ApplyPurchase>(j_token["applyPurchase"].ToString());

//            var sql = "select * from ApplyPurchase where 1=1 ";
//            if (applyPurchase.DepartmentId > 0)
//            {
//                sql += "and DepartmentId='" + applyPurchase.DepartmentId + "' ";
//            }
//            if (applyPurchase.ProjectId > 0)
//            {
//                sql += "and ProjectId='" + applyPurchase.ProjectId + "' ";
//            }
//            if (StartDate != "")
//            {
//                sql += "and Date(ApplyDate) >='" + StartDate + "' ";
//            }
//            if (EndDate != "")
//            {
//                sql += "and Date(ApplyDate) <='" + EndDate + "' ";
//            }
//            var query = _db.ApplyPurchase.FromSqlRaw(sql).OrderByDescending(y => y.ApplyDate)/*.OrderBy(x => x.State)*/;
//            var list = query.Where(x => !x.Delete).ToList();
//            var query1 = (from a in list
//                         join u in _db.Users on a.ApplyUserId equals u.Id
//                         join d in _db.Department on a.DepartmentId equals d.Id
//                         join p in _db.Project on a.ProjectId equals p.Id into projectData
//                         from pd in projectData.DefaultIfEmpty()
//                         select new { 
//                             a.ApplyPurchaseNumber,
//                             ApplyDate = a.ApplyDate.Date, 
//                             a.ApplyPurchaseCause, 
//                             a.TotalPrice, 
//                             a.DepartmentId, 
//                             a.ProjectId, 
//                             u.UserName, 
//                             d.DepartmentName, 
//                             ProjectName = pd == null ? "" : pd.ProjectName 
//                         }).ToList();
//            query1 = query1.OrderByDescending(x => x.ApplyDate).ToList();
//            return Json(query1);
//        }
//        #endregion

//        #region 采购报表
//        /// <summary>
//        /// 采购单数据
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="applyPurchase"></param>
//        /// <returns></returns>
//        [HttpPost("GetDetialPurchaseOrderReportData")]
//        public JsonResult GetDetialPurchaseOrderReportData([FromBody] dynamic formData)    
//        {
//            //(string StartDate, string EndDate, Purchase Purchase)
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            string StartDate = j_token["Start"].ToString();
//            string EndDate = j_token["End"].ToString();
//            Purchase Purchase = JsonConvert.DeserializeObject<Purchase>(j_token["Purchase"].ToString());

//            string sql = "SELECT * FROM `purchase` WHERE 1=1 AND `Delete`=0 ";
//            if (Purchase.DepartmentId > 0)
//            {
//                sql += $" AND `DepartmentId`='{ Purchase.DepartmentId }' ";
//            }
//            if (Purchase.ProjectId > 0)
//            {
//                sql += $" AND `ProjectId`='{ Purchase.ProjectId }' ";
//            }
//            if (StartDate != "")
//            {
//                sql += $" AND `PurchaseDate`>='{ StartDate }' ";
//            }
//            if (EndDate != "")
//            {
//                sql += $" AND `PurchaseDate`<='{ EndDate }' ";
//            }
//            var lis = _db.Purchase.FromSqlRaw(sql).ToList();
//            //20220328根据新业务逻辑，取消申购后，通过采购人获取信息
//            var data = (from a in lis
//                       join u in _db.Users on a.PurchaseUserId  equals u.Id
//                       join d in _db.Department on a.DepartmentId equals d.Id
//                       //join c in db.Customer on a.VendorInf equals c.Id 
//                       join p in _db.Project on a.ProjectId equals p.Id into projectData
//                       from pd in projectData.DefaultIfEmpty()
//                       select new { 
//                           a.PurchaseDate,
//                           d.DepartmentName, 
//                           d.Id, 
//                           ProjectName = pd == null ? "" : pd.ProjectName, 
//                           a.ProjectId, 
//                           u.UserName, 
//                           a.PurchaseUserId,
//                           a.VendorInf, 
//                           a.State, 
//                           a.PayMethod, 
//                           a.PurchaseNumber, 
//                           a.TotalPrice,
//                           a.PurchaseCause }).ToList();
//            data = data.OrderByDescending(x => x.PurchaseDate).ToList();
//            return Json(data);
//        }
//        #endregion

//        #region 入库单报表
//        /// <summary>
//        /// 入库单表主数据
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="inboundOrder"></param>
//        /// <returns></returns>
//        [HttpPost("GetDetialInboundorderReport")]
//        public JsonResult GetDetialInboundorderReport([FromBody] dynamic formData)
//        //(string StartDate, string EndDate, int ProjectId = 0, int DepartmentId = 0)
//        {
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            string StartDate = j_token["Start"].ToString();
//            string EndDate = j_token["End"].ToString();
//            int ProjectId = Convert.ToInt32(j_token["ProjectId"].ToString());
//            int DepartmentId = Convert.ToInt32(j_token["DepartmentId"].ToString());
//            var IRecord = _db.InboundRecord.Where(x => !x.Delete).ToList();
//            string sql = "SELECT * FROM `inboundorder` WHERE `Delete`=0 ";
//            if (StartDate != "")
//            {
//                sql += $" AND `OperatDate`>='{StartDate}' ";
//            }
//            if (EndDate != "")
//            {
//                sql += $" AND `OperatDate`<='{EndDate}' ";
//            }
//            var lis = _db.InboundOrder.FromSqlRaw(sql).ToList();
//            //采购单数据(获取采购单中的部门、项目的信息)
//            string sql2 = "SELECT * FROM `purchase` where purchase.Delete=0 ";
//            var purlis = _db.Purchase.FromSqlRaw(sql2).ToList();
//            var lisdata = (from a in lis
//                           join ir in IRecord on a.InboundNumber equals ir.InboundNumber
//                           join p in purlis on ir.PurchaseNumber equals p.PurchaseNumber into pur
//                           join de in _db.Device on ir.DeviceId equals de.Id
//                           join u in _db.Users on a.OperatId equals u.Id
//                           from pu in pur.DefaultIfEmpty()
//                           select new
//                           {
//                               de.DeviceName,
//                               de.DeviceModel,
//                               de.Unit,
//                               ir.Amount,
//                               ir.Price,
//                               ir.TotalPrice,
//                               ir.PurchaseNumber,
//                               a.InboundNumber,
//                               u.UserName,
//                               a.OperatDate,
//                               remark = a.Remark ?? "",
//                               DepartmentId = pu == null ? 0 : pu.DepartmentId,
//                               ProjectId = pu == null ? 0 : pu.ProjectId,
//                               cuname = pu == null ? 0 : pu.VendorInf,
//                               ProjectName = "",
//                               DepartmentName = ""
//                           }).ToList();
//            if (ProjectId != 0)
//            {
//                lisdata = lisdata.Where(x => x.ProjectId == ProjectId).ToList();
//            }
//            if (DepartmentId != 0)
//            {
//                lisdata = lisdata.Where(x => x.DepartmentId == DepartmentId).ToList();
//            }
//            lisdata = lisdata.OrderByDescending(x => x.OperatDate).ToList();
//            return Json(lisdata);
//        }
//        #endregion

//        #region 出库单报表
//        /// <summary>
//        /// 入库单表主数据
//        /// </summary>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <param name="outboundOrder"></param>
//        /// <returns></returns>
//        [HttpPost("GetDetialOutboundOrderReport")]
//        public JsonResult GetDetialOutboundOrderReport([FromBody] dynamic formData)
//        //(string StartDate, string EndDate, OutboundOrder outboundOrder, int DeviceCategoryIdOne)
//        {
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            string StartDate = j_token["Start"].ToString();
//            string EndDate = j_token["End"].ToString();
//            OutboundOrder outboundOrder = JsonConvert.DeserializeObject<OutboundOrder>(j_token["outboundOrder"].ToString());
//            int DeviceCategoryIdOne = Convert.ToInt32(j_token["DeviceCategoryIdOne"].ToString());

//            string sql = "SELECT * FROM `outboundorder` WHERE `Delete`=0 ";
//            if (StartDate != "")
//            {
//                sql += $" AND OperatDate>='{StartDate}' ";
//            }
//            if (EndDate != "")
//            {
//                sql += $" AND OperatDate<='{EndDate}' ";
//            }
//            if (outboundOrder.OutboundProject != 0)
//            {
//                sql += $" AND `OutboundProject`={ outboundOrder.OutboundProject } ";
//            }
//            if (outboundOrder.DepartmentId != 0)
//            {
//                sql += $" and `DepartmentId`={ outboundOrder.DepartmentId } ";
//            }
//            var lis = _db.OutboundOrder.FromSqlRaw(sql);
//            var data = (from a in lis
//                        join u in _db.Users on a.EmployeeId equals u.Id
//                        join d in _db.Department on a.DepartmentId equals d.Id
//                        join p in _db.Project on a.OutboundProject equals p.Id
//                        select new { a.OperatDate, d.DepartmentName, p.ProjectName, u.UserName, a.OutboundNumber, a.DepartmentId, a.OutboundProject, outboundRecordNew = a.outboundRecord == null ? new List<OutboundRecord>() : a.outboundRecord, TotalPrice = 0 }).ToList();
//            var devices = _db.Device.ToList();
//            if (DeviceCategoryIdOne != 0)
//                devices = devices.Where(x => x.DeviceCategoryIdOne == DeviceCategoryIdOne).ToList();
//            List<OutboundOrder> NewDatas = new List<OutboundOrder>();
//            foreach (var ot in data)
//            {
//                var OutboundRecordData = _db.OutboundRecord.Where(x => !x.Delete && x.OutboundNumber == ot.OutboundNumber).ToList();
//                foreach (var re in OutboundRecordData)
//                {
//                    var d = devices.Where(x => x.Id == re.DeviceId).FirstOrDefault();
//                    if (d != null)
//                    {
//                        //ot.TotalPrice += d.TotalPrice;
//                        ot.outboundRecordNew.Add(re);
//                    }
//                }
//            }
//            return Json(data.OrderBy(x => x.OperatDate));
//        }
//        #endregion

//        #region 汽车费用台账报表
//        /// <summary>
//        /// 获取相关台账数据
//        /// </summary>
//        /// <param name="offset"></param>
//        /// <param name="limit"></param>
//        /// <param name="CarId"></param>
//        /// <param name="StartDate"></param>
//        /// <param name="EndDate"></param>
//        /// <returns></returns>
//        [HttpPost("GetCarLedgerDB")]
//        public JsonResult GetCarLedgerDB([FromBody] dynamic formData)
//        //(int offset, int limit, string StartDate, string EndDate, int CarId = 0)
//        {
//            //序号	车辆品牌	车牌号	型号	保险费		维修、保养费	停车费/过路费	年检费	总公里数	油费	 
//            //获取所有汽车
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            string StartDate = j_token["Start"].ToString();
//            string EndDate = j_token["End"].ToString();
//            int CarId = Convert.ToInt32(j_token["CarId"].ToString());

//            List<CarLedgerModel> carList = new List<CarLedgerModel>();
//            try
//            {
//                var ALLVehicle = _db.VehicleManagement.Where(x => !x.Delete).ToList();
//                if (CarId != 0)
//                {
//                    ALLVehicle = ALLVehicle.Where(x => x.Id == CarId).ToList();
//                }
//                foreach (var item in ALLVehicle)
//                {
//                    CarLedgerModel carLedgerModel = new CarLedgerModel();
//                    carLedgerModel.CarId = item.Id;
//                    carList.Add(carLedgerModel);
//                }
//                var StartDatedt = Convert.ToDateTime(StartDate).AddDays(-1);
//                var EndDatedt = Convert.ToDateTime(EndDate).AddDays(1);
//                StartDate = StartDatedt.ToString("yyyy-MM-dd");
//                EndDate = EndDatedt.ToString("yyyy-MM-dd");
//                //获取或有维系记录等等
//                var ALLMaintenanceCar = _db.MaintenanceCar.Where(x => x.UpkeepDate >= StartDatedt && x.UpkeepDate <= EndDatedt && !x.Delete).ToList();
//                //获取所有汽车使用记录
//                var ALLUsageRecord = _db.UsageRecord.Where(x => string.Compare(x.EndTime.ToString(), StartDate) >= 0 && string.Compare(x.EndTime.ToString(), EndDate) <= 0 && !x.Delete).ToList();
//                //获取所有的油卡充值记录
//                var ALLGasCardsRecord = _db.GasCardsRecord.Where(x => string.Compare(x.RecordDate, StartDate) >= 0 && string.Compare(x.RecordDate, EndDate) <= 0 && !x.Delete).ToList();
//                //获取所有油卡
//                var GasCardslist = _db.GasCards.ToList();
//                //通过维修记录表获取数据
//                var list = (from c in ALLMaintenanceCar
//                            group c by c.CarId into g
//                            select new
//                            {
//                                CarId = g.Key,
//                                //保险费
//                                InsurancePremium = (from d in g where Convert.ToInt32(d.Maintenance) == (int)MaintenanceTypeEnum.Insurance select d.Price).Sum(),
//                                //维修
//                                MaintenanceFee = (from d in g where Convert.ToInt32(d.Maintenance) == (int)MaintenanceTypeEnum.Repair select d.Price).Sum(),
//                                //年检
//                                AnnualInspectionFee = (from d in g where Convert.ToInt32(d.Maintenance) == (int)MaintenanceTypeEnum.YearlyInspection select d.Price).Sum(),
//                            }).ToList();
//                for (int i = 0; i < list.Count(); i++)
//                {
//                    var CarInsurlist = carList.FindIndex(x => x.CarId == list[i].CarId);
//                    if (CarInsurlist != -1)
//                    {
//                        carList[CarInsurlist].InsurancePremium = list[i].InsurancePremium;
//                        carList[CarInsurlist].MaintenanceFee = list[i].MaintenanceFee;
//                        carList[CarInsurlist].AnnualInspectionFee = list[i].AnnualInspectionFee;

//                    }
//                }
//                var ALLUsageRecordlist = (from c in ALLUsageRecord
//                                          group c by c.CarId into g
//                                          select new
//                                          {
//                                              CarId = g.Key,
//                                              //获取过路费
//                                              RoadToll = (from d in g select d.WorkingCost).Sum(),
//                                              //获取总里程数 获取最大里程止数减最小里程开始数
//                                              TotalKilometres = Convert.ToDecimal((from d in g select d.MileageEnd).Max()) - Convert.ToDecimal((from d in g select d.MileageStart).Min()),
//                                          }).ToList();
//                var carIdArr = ALLVehicle.Select(x => x.Id).ToArray();
//                for (int i = 0; i < ALLUsageRecordlist.Count(); i++)
//                {
//                    int CarInsurlist = carList.FindIndex(x => x.CarId == ALLUsageRecordlist[i].CarId);
//                    if (CarInsurlist != -1)
//                    {
//                        carList[CarInsurlist].RoadToll = ALLUsageRecordlist[i].RoadToll;
//                        carList[CarInsurlist].TotalKilometres = ALLUsageRecordlist[i].TotalKilometres;
//                    }
//                }
//                // markers.Where(a => maps.Where(t => a.latitude == t.lat).Any()).ToList();  // 方法2
//                var GasId = GasCardslist.Where(j => ALLVehicle.Where(x => x.Id == j.CarId).Any()).ToList();
//                var ALLGasCardsRecordarr = (from c in ALLGasCardsRecord
//                                            group c by c.GasCardsId into g
//                                            select new
//                                            {
//                                                GasCardsId = g.Key,
//                                                //油费
//                                                OilFee = (from d in g select d.TopUpRecord).Sum(),
//                                            }).ToList();
//                var gascar = (from c in ALLGasCardsRecordarr
//                              join g in GasId on c.GasCardsId equals g.Id
//                              select new
//                              {
//                                  CarId = g.CarId,
//                                  OilFee = c.OilFee
//                              }).ToList();
//                for (int i = 0; i < gascar.Count(); i++)
//                {
//                    var CarInsurlist = carList.FindIndex(x => x.CarId == gascar[i].CarId);
//                    if (CarInsurlist != -1)
//                    {
//                        carList[CarInsurlist].OilFee = gascar[i].OilFee;
//                    }
//                }
//            }
//            catch (Exception)
//            {
//                throw;
//            }
//            // var carcount = carList.Count;
//            // carList = carList.Skip(limit * (offset - 1)).Take(limit).ToList();
//            return Json(carList);
//        }
//        #endregion

//        #region 油卡充值记录信息
//        /// <summary>
//        /// 获取所有油卡充值记录信息(倒叙)
//        /// </summary>
//        /// <returns></returns>
//        [HttpPost("GetAllGasCardsRecord")]
//        public JsonResult GetAllGasCardsRecord([FromBody] dynamic formData)
//        //(int ProjectId = 0, string EndDate = "", string StartDate = "", string CardsId = "")
//        {
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            string StartDate = j_token["Start"].ToString();
//            string EndDate = j_token["End"].ToString();
//            int CardsId = Convert.ToInt32(j_token["CardsId"].ToString());
//            int ProjectId = Convert.ToInt32(j_token["ProjectId"].ToString());

//            //获取所有油卡信息
//            var rows = _db.GasCardsRecord.Where(x => !x.Delete).OrderByDescending(x => x.RecordDate).ToList();
//            if (EndDate != "")
//            {
//                rows = rows.Where(x => Convert.ToDateTime(x.RecordDate) <= Convert.ToDateTime(EndDate)).ToList();
//            }
//            if (StartDate != "")
//            {
//                rows = rows.Where(x => Convert.ToDateTime(x.RecordDate) >= Convert.ToDateTime(StartDate)).ToList();
//            }
//            if (CardsId != 0)
//            {
//                rows = rows.Where(x => x.GasCardsId.Equals(CardsId)).ToList();
//            }
//            if (ProjectId != 0)
//            {
//                rows = rows.Where(x => x.ProjectId.Equals(ProjectId)).ToList();
//            }
//            /*
//               因为前台有直接CarId读取车辆的方法,所以这里我们 将油卡Id转换为CarId
//               为前台更加方便的转换
//             */
//            //foreach (var item in rows)
//            //{
//            //    item.GasCardsId = db.GasCards.Where(x => x.Id.Equals(item.GasCardsId)).FirstOrDefault().CarId;
//            //}
//            rows = rows.OrderByDescending(x => x.RecordDate).ThenByDescending(x => x.Id).ToList();
//            return Json(rows);
//        }
//        #endregion

//        #region 考勤汇总表
//        /// <summary>
//        /// 获取考勤数据
//        /// </summary>
//        /// <returns></returns>
//        [HttpPost("GetAttendanceSummaryData")]
//        public JsonResult GetAttendanceSummaryData([FromBody] dynamic formData)
//        {
//            JToken j_token = JsonConvert.DeserializeObject<JToken>(formData.ToString());
//            DateTime StartDate = Convert.ToDateTime(j_token["Start"].ToString());
//            DateTime EndDate = Convert.ToDateTime(j_token["End"].ToString());

//            List<punchclock> punchclocks = _db.punchclock.Where(x => x.todaytime >= StartDate && x.todaytime <= EndDate).ToList();
//            List<Users> users = PublicTool.GetAllOnTheJobUsers();
//            List<Attendance> attendances = new List<Attendance>();
//            PublicTool pu = new PublicTool();
//            #region 获取考勤设置
//            string date = StartDate.ToString("yyyy-MM");
//            var set = _db.AttendanceSet.Where(x => x.AttendanceMonth == date).FirstOrDefault();
//            #endregion
//            if (set != null)
//            {
//                #region 统计应出勤天数
//                var NoAttendanceDays = set.NoAttendanceDay.Split(',').ToList();
//                int MonthDays = DateTime.DaysInMonth(StartDate.Year, StartDate.Month);
//                int AttendanceDays = MonthDays - NoAttendanceDays.Count();
//                #endregion
//                foreach (var u in users)
//                {
//                    var roles = _db.Role.ToList().Where(x => pu.Compare(u.RoleId, x.Id)).Select(x => x.RoleNumber).ToList();
//                    if (!roles.Contains(PublicTool.Admin))
//                    {
//                        Attendance attendance = new Attendance();
//                        var ps = punchclocks.Where(x => x.number_DD == u.DD_number).ToList();
//                        attendance.UserId = u.Id;
//                        attendance.DepartmentId = u.DepartmentId;
//                        attendance.AttendanceDays = AttendanceDays;
//                        attendance.ActualAttendanceDays = AttendanceDays;
//                        string travelStartDate = "";
//                        string travelEndDate = "";
//                        string travelAddress = "";
//                        double travelDays = 0;
//                        foreach (var p in ps)
//                        {
//                            //#region 统计内勤天数,这儿存在统计错误，可能非工作日打卡，但是未去上班，会多统计一天，舍弃
//                            //if (NoAttendanceDays.Contains(p.todaytime.Day.ToString()) && !string.IsNullOrEmpty(p.onclicktime))
//                            //{
//                            //    attendance.ActualAttendanceDays++;
//                            //}
//                            //#endregion

//                            #region 统计出差情况
//                            //businesstravelday
//                            if (!string.IsNullOrEmpty(p.businesstravel))
//                            {
//                                string address = p.onclickaddress.Split('市')[0];
//                                string todaytime = p.todaytime.ToString("yyyy-MM-dd");
//                                if (!string.IsNullOrEmpty(address) && address != "固远晨通科技发展有限公司" && address != "四川省成都")
//                                {
//                                    if (travelAddress == address)
//                                    {
//                                        travelEndDate = todaytime;
//                                        travelDays += Convert.ToDouble(p.businesstravelday == "" ? "0" : p.businesstravelday);
//                                    }
//                                    else
//                                    {
//                                        if (!string.IsNullOrEmpty(travelAddress))
//                                        {
//                                            attendance.TravelDate += travelStartDate + "至" + travelEndDate + "</br>";
//                                            attendance.TravelAddress += travelAddress + " " + travelDays + "天" + "</br>";
//                                            travelDays = 0;
//                                        }
//                                        travelStartDate = todaytime;
//                                        travelEndDate = travelStartDate;
//                                        travelAddress = address;
//                                        travelDays += Convert.ToDouble(p.businesstravelday == "" ? "0" : p.businesstravelday);
//                                    }
//                                    if (p.todaytime.Day == ps.Count)
//                                    {
//                                        attendance.TravelDate += travelStartDate + "至" + travelEndDate + "</br>";
//                                        attendance.TravelAddress += travelAddress + " " + travelDays + "天" + "</br>";
//                                    }
//                                }
//                                else
//                                {
//                                    if (!string.IsNullOrEmpty(travelAddress))
//                                    {
//                                        attendance.TravelDate += travelStartDate + "至" + travelEndDate + "</br>";
//                                        attendance.TravelAddress += travelAddress + " " + travelDays + "天" + "</br>";
//                                        travelStartDate = "";
//                                        travelEndDate = "";
//                                        travelAddress = "";
//                                        travelDays = 0;
//                                    }
//                                }
//                                attendance.TravelDays += Convert.ToDouble(p.businesstravelday == "" ? "0" : p.businesstravelday);
//                                #region 统计内勤天数
//                                if (NoAttendanceDays.Contains(p.todaytime.Day.ToString()))
//                                {
//                                    attendance.ActualAttendanceDays = attendance.ActualAttendanceDays + Convert.ToDouble(p.businesstravelday == "" ? "0" : p.businesstravelday);
//                                }
//                                #endregion
//                            }
//                            else if (!string.IsNullOrEmpty(travelAddress))
//                            {
//                                attendance.TravelDate += travelStartDate + "至" + travelEndDate + "</br>";
//                                attendance.TravelAddress += travelAddress + " " + travelDays + "天" + "</br>";
//                                travelStartDate = "";
//                                travelEndDate = "";
//                                travelAddress = "";
//                                travelDays = 0;
//                            }
//                            #endregion

//                            #region 统计请假情况
//                            double leavedday = Convert.ToDouble(p.leavedday == "" ? "0" : p.leavedday);
//                            if (p.leaved)
//                            {
//                                if (p.leavetype == (int)LeaveEnum.compassionate_leave)
//                                {
//                                    attendance.ComLeave += leavedday;
//                                }
//                                else if (p.leavetype == (int)LeaveEnum.sick_leave)
//                                {
//                                    attendance.SickLeave += leavedday;
//                                }
//                                else if (p.leavetype == (int)LeaveEnum.annual_leave)
//                                {
//                                    attendance.AnnualLeave += leavedday;
//                                }
//                                else if (p.leavetype == (int)LeaveEnum.marriage_holiday)
//                                {
//                                    attendance.MarriageLeave += leavedday;
//                                }
//                                else if (p.leavetype == (int)LeaveEnum.Bereavement_leave)
//                                {
//                                    attendance.BereavementLeave += leavedday;
//                                }
//                                else if (p.leavetype == (int)LeaveEnum.Paternity_leave)
//                                {
//                                    attendance.MaternityLeave += leavedday;
//                                }
//                                attendance.ActualAttendanceDays = attendance.ActualAttendanceDays - leavedday;
//                            }
//                            #endregion

//                            #region 统计迟到、早退、缺卡、旷工情况
//                            if (p.late)
//                                attendance.LateCount++;
//                            if (p.leaveearly)
//                                attendance.LeaveEarlyCount++;
//                            if (p.lackOfCards)
//                                attendance.CardShortageCount++;
//                            if (p.absenteeism && !NoAttendanceDays.Contains(p.todaytime.Day.ToString()))
//                            {
//                                attendance.AbsenteeismCount++;
//                                attendance.ActualAttendanceDays = attendance.ActualAttendanceDays - 1 + leavedday;
//                            }
//                            #endregion

//                            #region 统计加班情况
//                            var addWork = _db.addworked.Where(x => x.number_DD == u.DD_number && x.todaytime.Year == p.todaytime.Year && x.todaytime.Month == p.todaytime.Month && x.todaytime.Day == p.todaytime.Day).FirstOrDefault();
//                            if (addWork != null)
//                            {
//                                //var addworktime = Convert.ToDateTime(addWork.addwork_Starttime) - Convert.ToDateTime(addWork.addwork_Endtime);
//                                var addworktime = Convert.ToDouble(addWork.addworktime);
//                                if (addWork.addworktype == ((int)AddWorkTypeEnum.WorkDay).ToString())
//                                {
//                                    attendance.WorkingDayOvertime += addworktime;
//                                }
//                                else if (addWork.addworktype == ((int)AddWorkTypeEnum.WeekEnd).ToString())
//                                {
//                                    attendance.WeekendOvertime += addworktime;
//                                }
//                                else if (addWork.addworktype == ((int)AddWorkTypeEnum.LegalHoliday).ToString())
//                                {
//                                    attendance.LegalHolidaysOvertime += addworktime;
//                                }
//                                #region 统计内勤天数
//                                if (NoAttendanceDays.Contains(p.todaytime.Day.ToString()))
//                                {
//                                    attendance.ActualAttendanceDays = attendance.ActualAttendanceDays + addworktime;
//                                }
//                                #endregion
//                            }
//                            #endregion
//                        }
//                        attendances.Add(attendance);
//                    }
//                }
//            }
//            return Json(attendances.OrderBy(x => x.DepartmentId).ToList());
//        }
//        #endregion

//        #endregion

//        #region 计算总费用公共方法
//        private List<ProjectFinanceReport> GetTotalCost(List<Project> queryProject)
//        {
//            var queryInvoices = _db.FinanceInvoice.Where(x => !x.Delete && x.State == (int)FinanceInvoiceState.Billing).GroupBy(x => new { x.ProjectId }).Select(y => new
//            {
//                y.Key.ProjectId,
//                CollectionAmount = y.Sum(x => x.CollectionAmount),
//                RateMoney = y.Sum(x => (x.TotalPrice / (1 + (x.TaxRate / 100))) * (x.TaxRate / 100))
//            }).ToList();
//            List<ProjectFinanceReport> projectFinanceReports = new List<ProjectFinanceReport>();
//            foreach (var p in queryProject)
//            {
//                ProjectFinanceReport pfr = new ProjectFinanceReport();
//                pfr.Id = p.Id;
//                pfr.ProjectNumber = p.ProjectNumber;
//                pfr.ProjectName = p.ProjectName;
//                pfr.ContractId = p.ContractId;
//                pfr.OperatDate = p.OperatDate;
//                pfr.CreateDate = p.CreateDate;
//                pfr.InFaceFinishDate = p.InFaceFinishDate;
//                pfr.SettleMoney = p.SettleMoney;
//                var c = _db.Contract.Where(x => x.Id == p.ContractId).FirstOrDefault();
//                if (c != null)
//                {
//                    pfr.Title = c.Title;
//                    pfr.SignDate = c.SignDate;
//                    pfr.CustomerId = c.CustomerId;
//                    pfr.ContractPrice = c.ContractPrice;
//                }
//                //获取审批通过的借款单
//                List<FinanceLoan> newFinanceLoans = new List<FinanceLoan>();
//                var financeLoans = _db.FinanceLoan.Where(x => p.Id == x.Project && !x.IsRefuse && !x.Delete).ToList();
//                foreach (var f in financeLoans)
//                {
//                    if (f.IsAuditEnd)
//                    {
//                        newFinanceLoans.Add(f);
//                    }
//                }
//                //获取审批通过的报销单
//                List<FinanceReimbursement> newFinanceReimbursements = new List<FinanceReimbursement>();
//                var financeReimbursements = _db.FinanceReimbursement.Where(x => p.Id == x.Project && !x.IsRefuse && !x.Delete).ToList();
//                foreach (var f in financeReimbursements)
//                {
//                    if (f.IsAuditEnd)
//                    {
//                        newFinanceReimbursements.Add(f);
//                    }
//                }
//                #region 个人借款
//                var financeIndividualLoans = newFinanceLoans.Where(x => x.FinanceType == (int)FinanceLoanResEnum.IndividualLoan).ToList();
//                pfr.PersonLoan = financeIndividualLoans.Sum(x => x.ApprovalMoney) - financeIndividualLoans.Sum(x => x.ReversalMoney) + p.PersonLoan;
//                #endregion
//                #region 采购未到票、人工费
//                var purchases = newFinanceLoans.Where(x => x.FinanceType == (int)FinanceLoanResEnum.PayApplication && !x.HasInvoice).ToList();
//                pfr.PurchaseCost = purchases.Sum(x => x.ApprovalMoney) - purchases.Sum(x => x.ReversalMoney) + p.PurchaseCost;
//                var LaborCosts = newFinanceLoans.Where(x => x.FinanceType == (int)FinanceLoanResEnum.PayApplication && x.HasInvoice).Select(x => x.LoanNumber).ToList();
//                if (LaborCosts.Count > 0)
//                {
//                    var LaborExpenses = _db.FinanceDocDetail.Where(x => LaborCosts.Contains(x.SourceNumber) && x.SourceType == (int)FinanceDocDetailEnum.Loan && x.CategoryOne == (int)FinanceCategoryEnum.LaborCost).ToList();
//                    var a = LaborExpenses.Sum(x => x.Money);
//                    pfr.LaborCost = a + p.LaborCost;
//                }
//                else
//                {
//                    pfr.LaborCost = p.LaborCost;
//                }
//                #endregion
//                #region 各种报销单据中具体类别的费用
//                pfr.MaterialCost = p.MaterialCost;
//                var newFinanceReimbursementNumbers = newFinanceReimbursements.Select(x => x.ReimNumber).ToList();
//                if (newFinanceReimbursementNumbers.Count > 0)
//                {
//                    var financeDocDetails = _db.FinanceDocDetail.Where(x => newFinanceReimbursementNumbers.Contains(x.SourceNumber) && x.SourceType == (int)FinanceDocDetailEnum.Reimbursement).ToList();
//                    pfr.MachineryCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.MachineryCost).Sum(x => x.Money) + p.MachineryCost;
//                    pfr.OtherDirectCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.OtherDirectCost).Sum(x => x.Money) + p.OtherDirectCost;
//                    pfr.SubcontractingCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.SubcontractingCost).Sum(x => x.Money) + p.SubcontractingCost;
//                    pfr.ManagementPersonCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.Test).Sum(x => x.Money) + p.ManagementPersonCost;
//                    pfr.WelfareCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.WelfareCost).Sum(x => x.Money) + p.WelfareCost;
//                    pfr.FixedAssetsCost = financeDocDetails.Where(x => x.CategoryTwo == (int)FinanceCategoryEnum.FixedAssetsCost).Sum(x => x.Money) + p.FixedAssetsCost;
//                    pfr.LowValueCost = financeDocDetails.Where(x => x.CategoryTwo == (int)FinanceCategoryEnum.LowValueCost).Sum(x => x.Money) + p.LowValueCost;
//                    pfr.OfficeCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.OfficeCost).Sum(x => x.Money) + p.OfficeCost;
//                    pfr.TravelCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.TravelCost).Sum(x => x.Money) + p.TravelCost;
//                    pfr.BusinessCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.BusinessCost).Sum(x => x.Money) + p.BusinessCost;
//                    pfr.SafetyCost = financeDocDetails.Where(x => x.CategoryTwo == (int)FinanceCategoryEnum.SafetyCost).Sum(x => x.Money) + p.SafetyCost;
//                    pfr.VehicleCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.VehicleCost).Sum(x => x.Money) + p.VehicleCost;
//                    pfr.OtherIndirectCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.OtherIndirectCost).Sum(x => x.Money) + p.OtherIndirectCost;
//                    pfr.OperatingCost = financeDocDetails.Where(x => x.CategoryOne == (int)FinanceCategoryEnum.OperatingCost).Sum(x => x.Money) + p.OperatingCost;
//                }
//                else
//                {
//                    pfr.MachineryCost = p.MachineryCost;
//                    pfr.OtherDirectCost = p.OtherDirectCost;
//                    pfr.SubcontractingCost = p.SubcontractingCost;
//                    pfr.ManagementPersonCost = p.ManagementPersonCost;
//                    pfr.WelfareCost = p.WelfareCost;
//                    pfr.FixedAssetsCost = p.FixedAssetsCost;
//                    pfr.LowValueCost = p.LowValueCost;
//                    pfr.OfficeCost = p.OfficeCost;
//                    pfr.TravelCost = p.TravelCost;
//                    pfr.BusinessCost = p.BusinessCost;
//                    pfr.SafetyCost = p.SafetyCost;
//                    pfr.VehicleCost = p.VehicleCost;
//                    pfr.OtherIndirectCost = p.OtherIndirectCost;
//                    pfr.OperatingCost = p.OperatingCost;
//                }
//                //冲销税额与开票已收款金额
//                var queryInvoice = queryInvoices.Where(x => x.ProjectId == p.Id).FirstOrDefault();
//                if (queryInvoice != null)
//                {
//                    pfr.WriteOffRate = (queryInvoice.RateMoney - p.WriteOffTax) + p.WriteOffRate;
//                    pfr.CollectionAmount = queryInvoice.CollectionAmount + p.CollectionAmount;
//                }
//                else
//                {
//                    pfr.WriteOffRate = p.WriteOffRate;
//                    pfr.CollectionAmount = p.CollectionAmount;
//                }
//                #endregion
//                #region 直接费用、间接费用、总费用以及占比
//                pfr.DirectTotalCost = pfr.PersonLoan + pfr.PurchaseCost + pfr.LaborCost + pfr.MaterialCost + pfr.MachineryCost + pfr.OtherDirectCost + pfr.SubcontractingCost + pfr.WriteOffRate;
//                pfr.IndirectTotalCost = pfr.ManagementPersonCost + pfr.WelfareCost + pfr.FixedAssetsCost + pfr.LowValueCost + pfr.OfficeCost + pfr.TravelCost + pfr.BusinessCost + pfr.SafetyCost + pfr.VehicleCost + pfr.OtherIndirectCost;
//                pfr.TotalCost = pfr.DirectTotalCost + pfr.IndirectTotalCost + pfr.OperatingCost;
//                pfr.DirectTotalCostRate = pfr.TotalCost == 0 ? 0 : (pfr.DirectTotalCost / pfr.TotalCost) * 100;
//                pfr.IndirectTotalCostRate = pfr.TotalCost == 0 ? 0 : (pfr.IndirectTotalCost / pfr.TotalCost) * 100;
//                pfr.OperatingCostRate = pfr.TotalCost == 0 ? 0 : (pfr.OperatingCost / pfr.TotalCost) * 100;
//                pfr.GrossProfit = pfr.CollectionAmount - pfr.TotalCost;
//                pfr.GrossProfitRate = pfr.CollectionAmount != 0 ? pfr.GrossProfit / pfr.CollectionAmount : 0;//毛利除以收入
//                #endregion
//                projectFinanceReports.Add(pfr);
//            }
//            return projectFinanceReports;
//        }
//        #endregion

//        #region 根据用户id和模块编号，查找相应的审批规则
//        /// <summary>
//        /// 根据用户id和模块编号，查找相应的审批规则
//        /// </summary>
//        /// <param name="ModuleNumber">模块编号</param>
//        /// <param name="SponsorUserid">数据对应用户id</param>
//        /// <returns></returns>
//        private AuditRule GetAuditRule(string ModuleNumber, int SponsorUserid)
//        {
//            AuditRule auditrule = null;
//            var user = _db.Users.Where(x => x.Id == SponsorUserid).FirstOrDefault();
//            if (user == null) return auditrule;
//            List<AuditRule> auditRules = _db.AuditRule.Where(x => !x.Delete && x.ModuleNumber == ModuleNumber).ToList();
//            foreach (var a in auditRules)
//            {
//                if (a.SponsorUser != null && a.SponsorUser.Split(',').Contains(SponsorUserid.ToString()))
//                {
//                    auditrule = a;
//                    break;
//                }
//            }
//            if (auditrule == null)
//            {
//                foreach (var a in auditRules)
//                {
//                    if (a.SponsorDepart != null && a.SponsorDepart.Split(',').Contains(user.DepartmentId.ToString()))
//                    {
//                        auditrule = a;
//                        break;
//                    }
//                }
//            }
//            return auditrule;
//        }
//        #endregion
//    }
//}
